package ch.ethz.inf.shop.model;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import ch.ethz.inf.shop.database.MySQLConnection;

/**
 * This class should be the interface between the web application and the
 * database. Keeping all the data-access methods here will be very helpful for
 * part 2 of the project.
 */
public final class DatastoreInterface {
	private static final Logger LOGGER = Logger.getLogger(DatastoreInterface.class);

	private final Connection sqlConnection;
	// product queries
	private static final String GET_ALL_PRODUCTS = "select * from products_with_likes";
	private static final String GET_PRODUCT_BY_ID = "select * from products_with_likes where product_id = ?";
	private static final String GET_PRODUCT_BY_NAME = "select * from products_with_likes where product_name like ?";
	private static final String GET_PRODUCT_BY_CATEGORY = "select * from products_with_likes where category_id = ?";
	private static final String GET_PRODUCT_BY_BRAND = "select * from products_with_likes where brand_id = ?";

	private static final String GET_PRODUCT_BY_BRAND_NAME = "select * from products_with_likes where brand_name like ?";

	private static final String GET_PRODUCT_BY_CATEGORY_NAME = "select * from products_with_likes where category_name like ?";
	// TODO: Implement correct query!
	private static final String GET_PRODUCT_BY_POPULARITY = GET_ALL_PRODUCTS + " order by like_cnt desc";

	// category queries
	private static final String GET_ALL_CATEGORIES = "select * from category";

	// brand queries
	private static final String GET_ALL_BRANDS = "select * from brand";

	private static final String GET_COMMENT_BY_PRODUCT_ID = "select * from comment, registered_user where comment.product = ? and comment.user = registered_user.user_id";

	private static final String GET_USER_BY_USERNAME_PW = "select * from registered_user where name = ? and passwd = SHA2(?,256)";

	// insert comment statement
	public static final String INSERT_COMMENT = "INSERT INTO comment (product, user, text, date) VALUES (?,?,?,NOW())";

	// insert new user
	public static final String INSERT_USER = "INSERT INTO registered_user (name, passwd, email) VALUES (?,SHA2(?,256),?)";
	// get user id from user name
	public static final String GET_USER_ID_FROM_NAME = "select user_id from registered_user where name = ?";

	// insert or replace like
	public static final String REPLACE_LIKE = "REPLACE `like` values (?,?)";

	// recipe queries
	private static final String GET_ALL_RECIPES = "SELECT * from recipe_with_prices";
	private static final String GET_RECIPE_BY_ID = "SELECT * FROM recipe_with_prices WHERE recipe_id = ?";
	private static final String GET_RECIPE_ITEMS_BY_RECIPE_ID = "select recipe_item.recipe_item_id, recipe_item.amount, products_with_likes.price*recipe_item.amount price, products_with_likes.* from recipe_item, products_with_likes where recipe_item.recipe=? AND recipe_item.product = products_with_likes.product_id";

	// Shopping cart queries
	private static final String GET_SHOPPING_CART_FOR_USER = "Select * from shopping_cart where user = ?";
	private static final String GET_SHOPPING_CART_LINE_FOR_PRODUCT_AND_USER = "Select * from shopping_cart_line where shopping_cart=? and product=?";
	private static final String UPDATE_CART_LINE_AMOUNT = "UPDATE shopping_cart_line SET `amount`=? WHERE shopping_cart_line_id=?";
	private static final String INSERT_CART_LINE = "INSERT INTO shopping_cart_line (`product`,`amount`,`shopping_cart`) VALUES (?,?,?)";
	private static final String GET_SHOPPING_CART_LINES_FOR_USER = "SELECT * FROM shopping_cart_lines WHERE user=?";
	private static final String GET_SHOPPING_CART_PRICE_FOR_USER = "SELECT SUM(line_price) total_price from shopping_cart_lines WHERE user=?";

	private static final String ADD_ONE_TO_SHOPPING_CART_LINE = "UPDATE shopping_cart_line SET amount = amount+1 WHERE shopping_cart_line_id=?";
	private static final String REMOVE_ONE_TO_SHOPPING_CART_LINE = "UPDATE shopping_cart_line SET amount = amount-1 WHERE shopping_cart_line_id=?";
	private static final String DELETE_SHOPPING_CART_LINE = "DELETE FROM shopping_cart_line WHERE shopping_cart_line_id=?";
	private static final String GET_SHOPPING_CART_LINE_BY_ID = "SELECT * FROM shopping_cart_line WHERE shopping_cart_line_id=?";

	private static final String INSERT_NEW_SHOPPING_CART_FOR_USER = "INSERT INTO shopping_cart (user) VALUES (?)";

	public DatastoreInterface() {
		this.sqlConnection = MySQLConnection.getInstance().getConnection();
	}

	public final Product getProductById(final int id) {
		LOGGER.debug("enter: getProductById(" + id + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_PRODUCT_BY_ID);
			stmt.setInt(1, id);

			LOGGER.debug("getProductById(): " + stmt.toString());
			rs = stmt.executeQuery();
			Product retVal = null;
			if (rs.next()) {
				retVal = new Product(rs);
			}

			return retVal;

		} catch (final SQLException ex) {
			LOGGER.error("error in getProductById():", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getProductById");
		}
	}

	public final List<Product> getAllProducts() {
		LOGGER.debug("enter: getAllProducts()");
		Statement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.createStatement();
			LOGGER.debug("getAllProducts(): " + GET_ALL_PRODUCTS);
			rs = stmt.executeQuery(GET_ALL_PRODUCTS);

			final List<Product> products = new ArrayList<Product>();
			while (rs.next()) {
				products.add(new Product(rs));
			}

			return products;

		} catch (final SQLException ex) {
			LOGGER.error("error in getAllProducts:", ex);
			return null;

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			}
			LOGGER.debug("leave: getAllProducts");
		}
	}

	public final List<Product> getProductsByName(final String name) {
		LOGGER.debug("enter: getProductsByName(" + name + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_PRODUCT_BY_NAME);
			stmt.setString(1, "%" + name + "%");

			LOGGER.debug("getProductsByName(): " + stmt.toString());
			rs = stmt.executeQuery();
			final List<Product> products = new ArrayList<Product>();
			while (rs.next()) {
				products.add(new Product(rs));
			}

			return products;

		} catch (final SQLException ex) {
			LOGGER.error("error in getProductsByName:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getProductsByName");
		}
	}

	public final List<Product> getProductsByCategory(final Category category) {
		LOGGER.debug("enter: getProductsByCategory(" + category + ")");

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_PRODUCT_BY_CATEGORY);
			stmt.setInt(1, category.getCategoryId());

			LOGGER.debug("getProductsByName(): " + stmt.toString());
			rs = stmt.executeQuery();

			final List<Product> products = new ArrayList<Product>();
			while (rs.next()) {
				products.add(new Product(rs));
			}

			return products;

		} catch (final SQLException ex) {
			LOGGER.error("error in getProductsByCategory:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getProductsByCategory");
		}

	}

	public final List<Product> getProductsByBrand(final Brand itemBrand) {
		LOGGER.debug("enter: getProductsByBrand(" + itemBrand + ")");

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_PRODUCT_BY_BRAND);
			stmt.setInt(1, itemBrand.getBrandId());

			LOGGER.debug("getProductsByName(): " + stmt.toString());
			rs = stmt.executeQuery();

			final List<Product> products = new ArrayList<Product>();
			while (rs.next()) {
				products.add(new Product(rs));
			}

			return products;

		} catch (final SQLException ex) {
			LOGGER.error("error in getProductsByBrand:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getProductsByBrand");
		}
	}

	public final List<Category> getAllCategories() {
		LOGGER.debug("enter: getAllCategories()");

		Statement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.createStatement();
			LOGGER.debug("getProductsByName(): " + GET_ALL_CATEGORIES);
			rs = stmt.executeQuery(GET_ALL_CATEGORIES);

			final List<Category> categories = new ArrayList<Category>();
			while (rs.next()) {
				categories.add(new Category(rs));
			}

			return categories;

		} catch (final SQLException ex) {
			LOGGER.error("error in getAllCategories:", ex);
			return null;

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			}
			LOGGER.debug("leave: getAllCategories");
		}
	}

	public final List<Brand> getAllBrands() {
		LOGGER.debug("enter: getAllBrands()");

		Statement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.createStatement();
			LOGGER.debug("getProductsByName(): " + GET_ALL_BRANDS);
			rs = stmt.executeQuery(GET_ALL_BRANDS);

			final List<Brand> brands = new ArrayList<Brand>();
			while (rs.next()) {
				brands.add(new Brand(rs));
			}

			return brands;

		} catch (final SQLException ex) {
			LOGGER.error("error in getAllBrands:", ex);
			return null;

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			}
			LOGGER.debug("leave: getAllBrands");
		}

	}

	public final List<Product> getProductsByPopularity() {
		LOGGER.debug("enter: getProductsByPopularity()");

		Statement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.createStatement();
			LOGGER.debug("getProductsByName(): " + GET_PRODUCT_BY_POPULARITY);
			rs = stmt.executeQuery(GET_PRODUCT_BY_POPULARITY);

			final List<Product> products = new ArrayList<Product>();
			while (rs.next()) {
				products.add(new Product(rs));
			}

			return products;

		} catch (final SQLException ex) {
			LOGGER.error("error in getProductsByPopularity:", ex);
			return null;

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			}
			LOGGER.debug("leave: getProductsByPopularity");
		}
	}

	public final List<Product> getProductsByBrandName(final String brandName) {
		LOGGER.debug("enter: getProductsByBrandName(" + brandName + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_PRODUCT_BY_BRAND_NAME);
			stmt.setString(1, "%" + brandName + "%");
			LOGGER.debug("getProductsByName(): " + stmt.toString());

			rs = stmt.executeQuery();

			final List<Product> products = new ArrayList<Product>();
			while (rs.next()) {
				products.add(new Product(rs));
			}

			return products;

		} catch (final SQLException ex) {
			LOGGER.error("error in getProductsByBrandName:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getProductsByBrandName");
		}
	}

	public final List<Product> getProductsByCategoryName(final String categoryName) {
		LOGGER.debug("enter: getProductsByCategoryName(" + categoryName + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_PRODUCT_BY_CATEGORY_NAME);
			stmt.setString(1, "%" + categoryName + "%");

			LOGGER.debug("getProductsByName(): " + stmt.toString());
			rs = stmt.executeQuery();

			final List<Product> products = new ArrayList<Product>();
			while (rs.next()) {
				products.add(new Product(rs));
			}

			return products;

		} catch (final SQLException ex) {
			LOGGER.error("error in getProductsByCategoryName:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getProductsByCategoryName");
		}
	}

	public final List<Comment> getCommentsByProduct(final Product product) {
		LOGGER.debug("enter: getCommentsByProduct(" + product + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_COMMENT_BY_PRODUCT_ID);
			stmt.setInt(1, product.getProductId());

			LOGGER.debug("getCommentsByProduct(): " + stmt.toString());
			rs = stmt.executeQuery();

			final List<Comment> comments = new ArrayList<Comment>();
			while (rs.next()) {
				Comment c = new Comment(rs);
				c.setProduct(product);
				c.setUser(new RegisteredUser(rs));
				comments.add(c);

			}

			return comments;

		} catch (final SQLException ex) {
			LOGGER.error("error in getCommentsByProduct:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getCommentsByProduct");
		}
	}

	public final RegisteredUser getUserDetailsFor(final String username, final String password) {
		LOGGER.debug("enter: getUserDetailsFor(" + username + ",******)");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_USER_BY_USERNAME_PW);
			stmt.setString(1, username);
			stmt.setString(2, password);

			LOGGER.debug("getUserDetailsFor(): " + stmt.toString());
			rs = stmt.executeQuery();

			if (rs.next()) {
				return new RegisteredUser(rs);

			}

			return null;

		} catch (final SQLException ex) {
			LOGGER.error("error in getUserDetailsFor:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error getUserDetailsFor ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error getUserDetailsFor Statement:", e);
				}
			LOGGER.debug("leave: getUserDetailsFor");
		}
	}

	public final void saveComment(final Comment c) {
		LOGGER.debug("enter: saveComment(" + c + ",******)");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(INSERT_COMMENT);
			stmt.setInt(1, c.getProduct().getProductId());
			stmt.setInt(2, c.getUser().getUserId());
			stmt.setString(3, c.getText());

			LOGGER.debug("saveComment(): " + stmt.toString());
			int affectedRows = stmt.executeUpdate();

			LOGGER.debug("saveComment() affectedRows: " + affectedRows);

		} catch (final SQLException ex) {
			LOGGER.error("error in saveComment:", ex);

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error saveComment ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error saveComment Statement:", e);
				}
			LOGGER.debug("leave: saveComment");
		}
	}

	public final void addLike(final RegisteredUser currentlyLoggedInUser, final Integer productID) {
		LOGGER.debug("enter: addLike(" + currentlyLoggedInUser + "," + productID + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(REPLACE_LIKE);
			stmt.setInt(1, currentlyLoggedInUser.getUserId());
			stmt.setInt(2, productID);

			LOGGER.debug("addLike(): " + stmt.toString());
			int affectedRows = stmt.executeUpdate();

			LOGGER.debug("addLike() affectedRows: " + affectedRows);

		} catch (final SQLException ex) {
			LOGGER.error("error in addLike:", ex);

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error addLike ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error addLike Statement:", e);
				}
			LOGGER.debug("leave: addLike");
		}

	}

	public final List<Recipe> getRecipes(String orderBy) {
		LOGGER.debug("enter: getRecipes()");
		Statement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.createStatement();
			String qry = GET_ALL_RECIPES;

			if ("price".equals(orderBy))
				qry = qry + " order by cost_of_ingredients";
			if ("name".equals(orderBy))
				qry = qry + " order by name";

			LOGGER.debug("getRecipes(): " + qry);

			rs = stmt.executeQuery(qry);
			final List<Recipe> recipes = new ArrayList<Recipe>();
			while (rs.next()) {
				recipes.add(new Recipe(rs));
			}

			return recipes;

		} catch (final SQLException ex) {
			LOGGER.error("error in getRecipes:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getRecipes");
		}
	}

	public final Recipe getRecipeById(final int id) {

		LOGGER.debug("enter: getRecipeById(" + id + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_RECIPE_BY_ID);
			stmt.setInt(1, id);

			LOGGER.debug("getRecipeById(): " + GET_ALL_PRODUCTS);
			rs = stmt.executeQuery();
			Recipe retVal = null;
			if (rs.next()) {
				retVal = new Recipe(rs);
			}

			return retVal;

		} catch (final SQLException ex) {
			LOGGER.error("error in getRecipeById():", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getRecipeById");
		}

	}

	public final List<RecipeItem> getRecipeItemsByRecipe(final Recipe recipe) {
		LOGGER.debug("enter: getRecipeItemsByRecipe(" + recipe + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_RECIPE_ITEMS_BY_RECIPE_ID);
			stmt.setInt(1, recipe.getRecipeId());

			LOGGER.debug("getRecipeItemsByRecipe(): " + stmt.toString());
			rs = stmt.executeQuery();

			final List<RecipeItem> recipes = new ArrayList<RecipeItem>();
			while (rs.next())
				recipes.add(new RecipeItem(rs, recipe));

			return recipes;

		} catch (final SQLException ex) {
			LOGGER.error("error in getRecipeItemsByRecipe:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getRecipeItemsByRecipe");
		}
	}

	public void addToShoppingCart(RegisteredUser currentlyLoggedInUser, Integer productId) {
		// TODO: must be executed in separate transaction
		LOGGER.debug("enter: addToShoppingCart(" + currentlyLoggedInUser + "," + productId + ")");
		PreparedStatement getUserStmt = null;
		PreparedStatement getShoppingCartLine = null;
		PreparedStatement updateStmt = null;

		ResultSet userResultSet = null;
		ResultSet shoppingCartLineResultSet = null;
		try {
			// get shopping cart for given user
			getUserStmt = this.sqlConnection.prepareStatement(GET_SHOPPING_CART_FOR_USER);
			getUserStmt.setInt(1, currentlyLoggedInUser.getUserId());

			LOGGER.debug("addToShoppingCart(): " + getUserStmt.toString());
			userResultSet = getUserStmt.executeQuery();

			if (!userResultSet.next()) {
				throw new Exception("No Shopping Cart for user with ID:  " + currentlyLoggedInUser.getUserId() + " found");
			}

			int shoppingCartId = userResultSet.getInt("shopping_cart_id");

			// get shopping cart line for given product and user
			getShoppingCartLine = this.sqlConnection.prepareStatement(GET_SHOPPING_CART_LINE_FOR_PRODUCT_AND_USER);
			getShoppingCartLine.setInt(1, shoppingCartId);
			getShoppingCartLine.setInt(2, productId);

			LOGGER.debug("addToShoppingCart(): " + getShoppingCartLine.toString());
			shoppingCartLineResultSet = getShoppingCartLine.executeQuery();

			// if already contains a line with this product
			// update it
			if (shoppingCartLineResultSet.next()) {
				int shoppingCartLine = shoppingCartLineResultSet.getInt("shopping_cart_line_id");
				int amount = shoppingCartLineResultSet.getInt("amount");
				updateStmt = sqlConnection.prepareStatement(UPDATE_CART_LINE_AMOUNT);
				updateStmt.setInt(1, amount + 1);
				updateStmt.setInt(2, shoppingCartLine);

			} else {// otherwise insert new line
				updateStmt = sqlConnection.prepareStatement(INSERT_CART_LINE);
				updateStmt.setInt(1, productId); // product
				updateStmt.setInt(2, 1);// amount
				updateStmt.setInt(3, shoppingCartId); // shopping_cart_id

			}
			LOGGER.debug("addToShoppingCart(): " + updateStmt.toString());
			if (updateStmt.executeUpdate() != 1) {
				throw new Exception("Could not insert shopping cart line");
			}

		} catch (final Exception ex) {
			LOGGER.error("error in addToShoppingCart:", ex);

		} finally {
			if (userResultSet != null)
				try {
					userResultSet.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (shoppingCartLineResultSet != null)
				try {
					shoppingCartLineResultSet.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (getUserStmt != null)
				try {
					getUserStmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			if (getShoppingCartLine != null)
				try {
					getShoppingCartLine.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			if (updateStmt != null)
				try {
					updateStmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: addToShoppingCart");
		}

	}

	public List<ShoppingCartLine> getShoppingCartLinesForUser(RegisteredUser user) {
		LOGGER.debug("enter: getShoppingCartLinesForUser(" + user + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(GET_SHOPPING_CART_LINES_FOR_USER);
			stmt.setInt(1, user.getUserId());

			LOGGER.debug("getShoppingCartLinesForUser(): " + stmt.toString());
			rs = stmt.executeQuery();

			final List<ShoppingCartLine> shoppingCartLines = new ArrayList<ShoppingCartLine>();
			while (rs.next()) {
				shoppingCartLines.add(new ShoppingCartLine(rs));
			}

			return shoppingCartLines;

		} catch (final SQLException ex) {
			LOGGER.error("error in getShoppingCartLinesForUser:", ex);
			return null;

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getShoppingCartLinesForUser");
		}

	}

	public BigDecimal getShoppingCartPriceForUser(RegisteredUser user) {

		LOGGER.debug("enter: getShoppingCartPriceForUser(" + user + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		BigDecimal retVal = new BigDecimal(0);
		try {

			stmt = this.sqlConnection.prepareStatement(GET_SHOPPING_CART_PRICE_FOR_USER);
			stmt.setInt(1, user.getUserId());

			LOGGER.debug("getShoppingCartPriceForUser(): " + stmt.toString());
			rs = stmt.executeQuery();

			if (rs.next()) {
				retVal = rs.getBigDecimal("total_price");
				if (retVal == null)
					retVal = new BigDecimal(0);
			}
			return retVal;

		} catch (final SQLException ex) {
			LOGGER.error("error in getShoppingCartPriceForUser:", ex);
			return new BigDecimal(-1);

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error closing ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error closing Statement:", e);
				}
			LOGGER.debug("leave: getShoppingCartPriceForUser");
		}

	}

	public void addOneToShoppingCartLine(int cartLineId) {
		LOGGER.debug("enter: addOneToShoppingCartLine(" + cartLineId + ")");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			stmt = this.sqlConnection.prepareStatement(ADD_ONE_TO_SHOPPING_CART_LINE);
			stmt.setInt(1, cartLineId);
			LOGGER.debug("addOneToShoppingCartLine(): " + stmt.toString());
			int affectedRows = stmt.executeUpdate();

			LOGGER.debug("addOneToShoppingCartLine() affectedRows: " + affectedRows);

		} catch (final SQLException ex) {
			LOGGER.error("error in addOneToShoppingCartLine:", ex);

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error Statement:", e);
				}
			LOGGER.debug("leave: addOneToShoppingCartLine");
		}
	}

	public void removeOneFromShoppingCartLine(int cartLineId) {
		LOGGER.debug("enter: removeOneFromShoppingCartLine(" + cartLineId + ")");
		PreparedStatement getLineStmt = null;
		PreparedStatement updateStmt = null;
		ResultSet getLineResultSet = null;
		try {

			// get line id for given user
			getLineStmt = this.sqlConnection.prepareStatement(GET_SHOPPING_CART_LINE_BY_ID);
			getLineStmt.setInt(1, cartLineId);

			LOGGER.debug("removeOneFromShoppingCartLine(): " + getLineStmt.toString());
			getLineResultSet = getLineStmt.executeQuery();

			if (!getLineResultSet.next()) {
				throw new Exception("No shopping cart line with ID:  " + cartLineId + " found");
			}
			int amount = getLineResultSet.getInt("amount");

			// if amount is only one, delete whole row
			if (amount == 1) {
				updateStmt = this.sqlConnection.prepareStatement(DELETE_SHOPPING_CART_LINE);
			} else {// only subtract one from amount
				updateStmt = this.sqlConnection.prepareStatement(REMOVE_ONE_TO_SHOPPING_CART_LINE);
			}

			updateStmt.setInt(1, cartLineId);
			LOGGER.debug("removeOneFromShoppingCartLine(): " + updateStmt.toString());
			int affectedRows = updateStmt.executeUpdate();

			LOGGER.debug("removeOneFromShoppingCartLine() affectedRows: " + affectedRows);

		} catch (final Exception ex) {
			LOGGER.error("error in removeOneFromShoppingCartLine:", ex);
		} finally {
			if (getLineResultSet != null)
				try {
					getLineResultSet.close();
				} catch (SQLException e) {
					LOGGER.error("error ResultSet:", e);
				}
			if (getLineStmt != null)
				try {
					updateStmt.close();
				} catch (SQLException e) {
					LOGGER.error("error Statement:", e);
				}
			if (getLineStmt != null)
				try {
					updateStmt.close();
				} catch (SQLException e) {
					LOGGER.error("error Statement:", e);
				}
			LOGGER.debug("leave: removeOneFromShoppingCartLine");
		}
	}

	public RegisteredUser createUser(String username, String email, String password) {

		// 1. registration: insert the user into the database
		LOGGER.debug("enter: create user ( " + username + ", " + email + ", " + password + " )");
		PreparedStatement stmt = null;

		try {

			stmt = this.sqlConnection.prepareStatement(INSERT_USER);
			stmt.setString(1, username);
			stmt.setString(2, password);
			stmt.setString(3, email);

			LOGGER.debug("saveUser(): " + stmt.toString());

			int affectedRows = stmt.executeUpdate();

			LOGGER.debug("savaUser() affectedRows: " + affectedRows);

		} catch (final SQLException ex) {
			LOGGER.error("error in createUser when saving user:", ex);
			return new RegisteredUser(null, null, null, -1);

		} finally {

			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error saveComment Statement:", e);
				}

			LOGGER.debug("user added to table in method createUser()");
		}

		// TODO: 2. get the new registered user (especially its id and return
		// it)

		int id = -1;
		ResultSet rs = null;
		try {
			stmt = this.sqlConnection.prepareStatement(GET_USER_ID_FROM_NAME);
			stmt.setString(1, username);
			rs = stmt.executeQuery();

			if (rs.next()) {
				id = rs.getInt("user_id");
			}

		} catch (final SQLException ex) {
			LOGGER.error("error in createUser getting user id from name:", ex);

		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					LOGGER.error("error createUser ResultSet:", e);
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error createUser Statement:", e);
				}
		}

		// TODO: 1.5 add an entry in shopping-cart as well
		stmt = null;

		try {
			stmt = this.sqlConnection.prepareStatement(INSERT_NEW_SHOPPING_CART_FOR_USER);
			stmt.setInt(1, id);
			stmt.execute();

		} catch (SQLException ex) {
			LOGGER.error("error in createUser creating shopping cart:", ex);
		} finally {

			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.error("error createUser creating shopping cart:", e);
				}
		}

		LOGGER.debug("leaving method createUser()");
		return new RegisteredUser(username, password, email, id);
	}
}
